package com.example.DemoDataeaseExample.provider;
import java.sql.*;
import java.util.Properties;
import java.util.HashMap;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import com.example.DemoDataeaseExample.config.KingbaseConfig;
import com.example.DemoDataeaseExample.entity.ConnectionObj;
import com.example.DemoDataeaseExample.entity.DatasourceDTO;
import com.example.DemoDataeaseExample.entity.DatasourceSchemaDTO;
import com.example.DemoDataeaseExample.type.Kingbase;
import org.apache.calcite.config.CalciteConnectionProperty;
import org.apache.calcite.sql.validate.SqlConformanceEnum;
import org.apache.commons.dbcp2.BasicDataSource;
import org.apache.calcite.adapter.jdbc.JdbcSchema;
import org.apache.calcite.jdbc.CalciteConnection;
import org.apache.calcite.schema.SchemaPlus;
public class KingbaseProvider extends Provider {

    private ExtendedJdbcClassLoader jdbcClassLoader;
    private final Map<String, BasicDataSource> dataSourceMap = new HashMap<>();

    // 静态初始化块，在类加载时配置系统属性
    static {
        // 设置默认字符集
        System.setProperty("calcite.default.charset", "utf8");
        System.setProperty("file.encoding", "UTF-8");
    }

    public KingbaseProvider() {
        try {
            // 初始化JDBC驱动加载器
            this.jdbcClassLoader = new ExtendedJdbcClassLoader(
                    new java.net.URL[]{}, Thread.currentThread().getContextClassLoader());
        } catch (Exception e) {
            throw new RuntimeException("初始化驱动加载器失败", e);
        }
    }

    @Override
    public ConnectionObj getConnection(DatasourceDTO datasource) throws Exception {
        ConnectionObj connectionObj = new ConnectionObj();

        // 解析配置
        Kingbase configuration = new Kingbase();
        configuration.setHost(KingbaseConfig.DEFAULT_HOST);
        configuration.setPort(Integer.parseInt(KingbaseConfig.DEFAULT_PORT));
        configuration.setDataBase(KingbaseConfig.DEFAULT_DATABASE);
        configuration.setUsername(datasource.getUsername());
        configuration.setPassword(datasource.getPassword());

        Properties props = new Properties();
        props.setProperty("user", configuration.getUsername());
        props.setProperty("password", configuration.getPassword());

        String driverClassName = configuration.getDriver();

        try {
            Driver driverClass = (Driver) jdbcClassLoader.loadClass(driverClassName).newInstance();
            Connection conn = driverClass.connect(datasource.getJdbcUrl(), props);
            connectionObj.setConnection(conn);
        } catch (Exception e) {
            throw new Exception("连接数据库失败: " + e.getMessage());
        }

        return connectionObj;
    }

    @Override
    public String checkStatus(DatasourceDTO datasource) throws Exception {
        ConnectionObj connectionObj = null;
        Statement stmt = null;
        ResultSet rs = null;

        try {
            connectionObj = getConnection(datasource);
            stmt = getStatement(connectionObj.getConnection(), datasource.getQueryTimeout());
            rs = stmt.executeQuery("SELECT 1");

            if (rs.next()) {
                return "Success";
            }
            return "Failed";
        } catch (Exception e) {
            throw new Exception("检查连接状态失败: " + e.getMessage());
        } finally {
            if (rs != null) {
                try { rs.close(); } catch (SQLException e) { /* 忽略关闭错误 */ }
            }
            if (stmt != null) {
                try { stmt.close(); } catch (SQLException e) { /* 忽略关闭错误 */ }
            }
            if (connectionObj != null && connectionObj.getConnection() != null) {
                try { connectionObj.getConnection().close(); } catch (SQLException e) { /* 忽略关闭错误 */ }
            }
        }
    }

    // 获取Calcite连接
    public Connection getCalciteConnection(DatasourceDTO datasource) {
        try {
            // 注册驱动
            Driver driver = (Driver) jdbcClassLoader.loadClass(KingbaseConfig.KINGBASE_DRIVER).newInstance();
            DriverManager.registerDriver(new DriverShim(driver));

            // 创建连接
            Properties info = new Properties();
            info.setProperty("lex", "JAVA");
            // 字符集配置
            info.setProperty("charset", "UTF-8");
            info.setProperty("calcite.default.charset", "utf8");

            // SQL一致性配置
            info.setProperty(CalciteConnectionProperty.CONFORMANCE.camelName(),
                    SqlConformanceEnum.LENIENT.name());

            Class.forName("org.apache.calcite.jdbc.Driver");
            Connection connection = DriverManager.getConnection("jdbc:calcite:", info);

            // 构建Schema
            CalciteConnection calciteConnection = connection.unwrap(CalciteConnection.class);
            SchemaPlus rootSchema = calciteConnection.getRootSchema();

            // 创建数据源
            BasicDataSource dataSource = new BasicDataSource();
            dataSource.setDriverClassName(KingbaseConfig.KINGBASE_DRIVER);
            dataSource.setUrl(datasource.getJdbcUrl());
            dataSource.setUsername(datasource.getUsername());
            dataSource.setPassword(datasource.getPassword());
            dataSource.setInitialSize(5);
            dataSource.setMaxTotal(20);
            dataSource.setMinIdle(5);
            dataSource.setMaxWaitMillis(60000);

            // 设置连接验证参数，确保连接有效
            dataSource.setTestOnBorrow(true);
            dataSource.setTestOnReturn(true);
            dataSource.setTestWhileIdle(true);
            dataSource.setValidationQuery("SELECT 1");

            // 添加JDBC Schema
            JdbcSchema schema = JdbcSchema.create(
                    rootSchema,
                    "KINGBASE",
                    dataSource,
                    null,
                    "public"
            );
            rootSchema.add("KINGBASE", schema);

            dataSourceMap.put(datasource.getName(), dataSource);

            return connection;
        } catch (Exception e) {
            throw new RuntimeException("获取Calcite连接失败: " + e.getMessage());
        }
    }

    // 支持SQL方言转换
    public String convertSql(String sql) {
        try {
            DatasourceSchemaDTO schema = new DatasourceSchemaDTO("kingbase");
            Map<Long, DatasourceSchemaDTO> dsMap = new HashMap<>();
            dsMap.put(1L, schema);

            // 添加调试信息
            System.out.println("开始转换SQL方言: " + sql);
            String result = transSqlDialect(sql, dsMap);

            // 移除双引号
            result = result.replace("\"", "");

            // 处理Unicode转义序列
            result = processUnicodeEscapes(result);

            System.out.println("转换完成: " + result);
            return result;
        } catch (Exception e) {
            System.err.println("SQL方言转换失败: " + e.getMessage());
            e.printStackTrace();
            // 转换失败时返回原SQL
            return sql;
        }
    }

    /**
     * 处理SQL中的Unicode转义序列
     * 将u&'\xxxx\xxxx'格式的Unicode转义序列转换回普通字符串
     */
    private String processUnicodeEscapes(String sql) {
        // 匹配u&'\xxxx\xxxx...'格式
        Pattern pattern = Pattern.compile("u&'(\\\\[0-9a-fA-F]{4})+?'");
        Matcher matcher = pattern.matcher(sql);

        StringBuffer sb = new StringBuffer();
        while (matcher.find()) {
            try {
                String unicodeEscape = matcher.group();
                // 提取Unicode码点
                String content = unicodeEscape.substring(3, unicodeEscape.length() - 1);

                StringBuilder plainText = new StringBuilder();
                // 分割成单独的码点(\xxxx)
                String[] hexValues = content.split("\\\\");
                // 第一个元素是空字符串，因为字符串以\开头
                for (int i = 1; i < hexValues.length; i++) {
                    // 将16进制转换为int，再转为字符
                    int codePoint = Integer.parseInt(hexValues[i], 16);
                    plainText.appendCodePoint(codePoint);
                }

                // 使用单引号括起来，确保SQL语法正确
                String replacement = "'" + plainText.toString().replace("'", "''") + "'";
                matcher.appendReplacement(sb, Matcher.quoteReplacement(replacement));
            } catch (Exception e) {
                System.err.println("处理Unicode转义序列失败: " + e.getMessage());
                // 处理失败时不替换，保持原样
            }
        }
        matcher.appendTail(sb);
        return sb.toString();
    }

    // 确保在程序结束时关闭所有数据源
    public void close() {
        for (BasicDataSource dataSource : dataSourceMap.values()) {
            try {
                if (dataSource != null && !dataSource.isClosed()) {
                    dataSource.close();
                }
            } catch (SQLException e) {
                System.err.println("关闭数据源失败: " + e.getMessage());
            }
        }
        dataSourceMap.clear();
    }
}